from sqlalchemy import create_engine
import tushare as ts
import talib
import numpy as np
import pandas as pd
import psycopg2 as pg
import math
from datetime import timedelta, date

def handdata():
	#从数据库里查日期和code
	# 连接数据库
    try:
        conn = pg.connect(dbname='postgres', host='127.0.0.1', user='lengdh', password='123456',
                   port='5432')
        cur = conn.cursor()
        cur.execute(
           #"select date,code  from basic_data  where dif is  null order by date desc") ##跑历史
           "select date,code  from basic_data  where  to_date(date,'YYYY-MM-DD')='2018-01-05'") ##跑当天
        rows = cur.fetchall()
        #rows=[['2017-12-07','002828']]
        for r in rows:
            print(r)
            #df = ts.get_k_data(r[1], ktype='D' , start='2016-06-15', end=r[0])
            #print(df)
            #print('==============')
            cur.execute("select close  from tick_d_data where code = %s and to_date(date,'YYYY-MM-DD') <= %s order by date ",(r[1],r[0]))#价格一定要按日期排序，否则macd不一致
            prices=cur.fetchall()
            #print((np.array(prices)).flatten())
            macd,dif,dea = MACD((np.array(prices)).flatten(), fastperiod=12, slowperiod=26, signalperiod=9)
            #macd2 = MACD(df['close'].values, fastperiod=12, slowperiod=26, signalperiod=9)
            if math.isnan(dif):
            	dif=None
            	dea=None 
            	macd=None
            else:
            	dif=round(dif,2)
            	dea=round(dea,2)
            	macd=round(macd,2)
            print(macd,dif,dea)
            #计算完了macd要写到数据
            cur.execute("update basic_data set macd = %s where date = %s and code = %s",(macd,r[0],r[1]))
            cur.execute("update basic_data set dif = %s where date = %s and code = %s",(dif,r[0],r[1]))
            cur.execute("update basic_data set dea = %s where date = %s and code = %s",(dea,r[0],r[1]))
            conn.commit()
    except Exception as e:
        print ('Error: ' + e)
    print("Done=======")
    cur.close()
    conn.close()
# 定义MACD函数  
def MACD(prices, fastperiod=12, slowperiod=26, signalperiod=9):
    '''
    参数设置:
        fastperiod = 12
        slowperiod = 26
        signalperiod = 9

    返回: dif - dea
    '''
    dif, dea, hist = talib.MACD(prices, 
                                    fastperiod=fastperiod, 
                                    slowperiod=slowperiod, 
                                    signalperiod=signalperiod)
    return (dif[-1] - dea[-1])*2,dif[-1],dea[-1]
if __name__ == "__main__":
	handdata()